/*
 * Copyright (c) 2005-2008 旭鸣信息
 * All rights reserved. 
 */
/*
 * History:
 *       2007-12-10: Initially created, tangkf.
 */
package com.xteam.db;

import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.tangkf.utils.CommonUtils;
import com.xteam.db.page.Page;
import com.xteam.db.page.PageObjectSet;

/**
 * <p>
 * 实现数据库基本操作 支持HQL 与 SQL两种类型的语句
 * </p>
 * @author tangkf
 */
public class BaseDao implements IBaseDao {

	private static BaseDao	instance	= new BaseDao();

	public static BaseDao getInstance() {
		return instance;
	}

	/**
	 * 获取一个 hibernate 的session
	 */
	public Session getSession() {
		return SessionProvider.getSession();
	}

	/**
	 * 获取一个 hibernate 的session
	 */
	public void releaseSession(Session ses) {
		SessionProvider.releaseSession(ses);
	}

	/**
	 * @return
	 * @athor tangkf
	 * @date 2008-1-4
	 */
	@SuppressWarnings("unchecked")
	public <O> O getObject(Class<O> clazz, String id) throws Exception {
		O obj = null;
		Session ses = this.getSession();
		obj = (O) ses.load(clazz, id);
		this.releaseSession(ses);
		return obj;
	}

	/**
	 * 执行一个HQL语句，并返回第一个对象
	 * @return
	 * @athor tangkf
	 * @date 2008-1-4
	 */
	public <T> T getObject(String hql) throws Exception {
		T obj = null;
		List<T> lst = this.queryAll(hql);
		if (lst != null && lst.size() > 0) {
			obj = (T) lst.get(0);
		}
		return obj;
	}

	/**
	 * 删除一个对象
	 */
	public int delete(Object object) throws Exception {
		int rst = 0;
		Session ses = this.getSession();
		Transaction tx = ses.beginTransaction();
		try {
			// ---删除单个对象---
			if (object != null)
				ses.delete(object);
			ses.flush();
			tx.commit();
		} catch (RuntimeException e) {
			rst = 1;
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			if (ses != null) {
				this.releaseSession(ses);
			}
		}
		return rst;
	}

	/**
	 * 根据传入的ID参数，批量删除记录
	 * @author Implements by tangkf
	 * @Date 2008-1-6
	 */
	public <T> int deleteBathById(Class<T> clazz, String idName, String[] idValues) throws Exception {
		String hql = IBaseDao.SQL_DELETE + IBaseDao.SQL_FROM + clazz.getName() + IBaseDao.SQL_WHERE + idName + IBaseDao.SQL_IN + "(:inlist)";
		int r = 0;
		Session ses = this.getSession();
		Transaction tx = null;
		try {
			tx = ses.beginTransaction();
			Query q = ses.createQuery(hql);
			q.setParameterList("inlist", idValues);
			q.executeUpdate();
			tx.commit();
		} catch (RuntimeException e) {
			if (null != tx) {
				tx.rollback();
			}
			r = 1;
			throw e;
		} finally {
			this.releaseSession(ses);
		}
		return r;
	}

	/**
	 * 删除一个对象列表
	 */
	public <T> int deleteObjects(List<T> objects) throws Exception {
		int rst = 0;
		Session ses = this.getSession();
		Transaction tx = ses.beginTransaction();
		try {
			// ---批量删除---
			List<T> l = (List<T>) objects;
			Iterator<T> it = l.iterator();
			while (it.hasNext()) {
				Object o = it.next();
				if (o != null)
					ses.delete(o);
				ses.flush();
			}
			tx.commit();
		} catch (RuntimeException e) {
			rst = 1;
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			if (ses != null)
				this.releaseSession(ses);
		}
		return rst;
	}

	/**
	 * @author Implements by tangkf
	 * @Date 2008-1-12
	 */
	public int saveObject(Object obj) throws Exception {
		int rst = 0;
		Session ses = this.getSession();
		Transaction tx = ses.beginTransaction();
		try {
			// ---保存单个对象---
			if (obj != null)
				ses.save(obj);
			ses.flush();
			tx.commit();
		} catch (RuntimeException e) {
			rst = 1;
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			if (ses != null)
				this.releaseSession(ses);
		}
		return rst;
	}

	/**
	 * @author Implements by tangkf
	 * @Date 2008-1-12
	 */
	public int saveObjects(List<Object> objects) throws Exception {
		int rst = 0;
		Session ses = this.getSession();
		Transaction tx = ses.beginTransaction();
		try {
			// ---批量保存---
			List<Object> l = (List<Object>) objects;
			Iterator<Object> it = l.iterator();
			while (it.hasNext()) {
				Object o = it.next();
				if (o != null)
					ses.save(o);
				ses.flush();
			}
			tx.commit();
		} catch (RuntimeException e) {
			rst = 1;
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			if (ses != null)
				this.releaseSession(ses);
		}
		return rst;
	}

	/**
	 * @author Implements by tangkf
	 * @Date 2008-1-12
	 */
	public int saveOrUpdateObject(Object object) throws Exception {
		int rst = 0;
		Session ses = this.getSession();
		Transaction tx = ses.beginTransaction();
		try {
			// ---单个更新---
			if (object != null)
				ses.saveOrUpdate(object);
			ses.flush();
			tx.commit();
		} catch (RuntimeException e) {
			rst = 1;
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			if (ses != null)
				this.releaseSession(ses);
		}
		return rst;
	}

	/**
	 * @author Implements by tangkf
	 * @Date 2008-1-12
	 */
	public int saveOrUpdateObjects(List<Object> objects) throws Exception {
		int rst = 0;
		Session ses = this.getSession();
		Transaction tx = ses.beginTransaction();
		try {
			// ---批量更新---
			List<Object> l = objects;
			Iterator<Object> it = l.iterator();
			while (it.hasNext()) {
				Object o = it.next();
				if (o != null) {
					ses.saveOrUpdate(o);
					ses.flush();
				}
			}
			tx.commit();
		} catch (RuntimeException e) {
			rst = 1;
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			if (ses != null)
				this.releaseSession(ses);
		}
		return rst;
	}

	/**
	 * @author Implements by tangkf
	 * @Date 2008-1-12
	 */
	public int updateObject(Object object) throws Exception {
		int rst = 0;
		Session ses = this.getSession();
		Transaction tx = ses.beginTransaction();
		try {
			// ---单个更新---
			if (object != null)
				ses.update(object);
			ses.flush();
			tx.commit();
		} catch (RuntimeException e) {
			rst = 1;
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			if (ses != null)
				this.releaseSession(ses);
		}
		return rst;
	}

	/**
	 * @author Implements by tangkf
	 * @Date 2008-1-12
	 */
	public int updateObjects(List<Object> objects) throws Exception {
		int rst = 0;
		Session ses = this.getSession();
		Transaction tx = ses.beginTransaction();
		try {
			// ---批量更新---
			List<Object> l = objects;
			Iterator<Object> it = l.iterator();
			while (it.hasNext()) {
				Object o = it.next();
				if (o != null) {
					ses.update(o);
					ses.flush();
				}
			}
			tx.commit();
		} catch (RuntimeException e) {
			rst = 1;
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			if (ses != null)
				this.releaseSession(ses);
		}
		return rst;
	}

	/**
	 * 分页查询，显示的要求传递 记录总数统计HQL语句
	 * @author tangkf ---2012-1-13
	 * @param <T>
	 * @param hql 查询数据的HQL语句
	 * @param countHql 查询总记录数量的HQL语句
	 * @param params 查询参数
	 * @param index 起始行号
	 * @param size 每页行数
	 * @param queryRowCount 是否强制执行总记录数查询
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private <T> PageObjectSet<T> queryPage(String hql, String countHql, Object params, int index, int size, boolean queryRowCount) throws Exception {
		PageObjectSet<T> pos = null;
		Session ses = null;
		try {
			long rowCount = 0;
			// 计算总记录数
			if (!CommonUtils.isEmpty(countHql)) {
				rowCount = this.countHql(countHql);
			} else if (queryRowCount) {
				// 如果没有总记录数语句，且强制执行了总记录数查询
				// TODO 强制查询总记录数
			}

			ses = this.getSession();
			List<T> list = null;
			Query query = ses.createQuery(hql);

			// 参数设置
			if (params != null) {
				if (params instanceof Map) {
					Map<String, Object> mparam = (Map<String, Object>) params;
					Iterator<Entry<String, Object>> it = mparam.entrySet().iterator();
					while (it.hasNext()) {
						Entry<String, Object> e = it.next();
						String key = e.getKey();
						Object value = mparam.get(key);
						query.setParameter(key, value);
					}
				} else if (params instanceof Object[]) {
					Object[] oparam = (Object[]) params;
					for (int i = 0; i < oparam.length; i++) {
						query.setParameter(i, oparam[i]);
					}
				}
			}

			// 设置分页信息
			query.setFirstResult(index);
			query.setMaxResults(size);

			// 执行查询
			list = query.list();

			// 组装分页对象
			pos = new PageObjectSet<T>(rowCount, list);
			pos.setResultList(list);
			pos.setIndex(index);
			pos.setSize(size); // 设置后可自动计算页数

		} catch (RuntimeException e) {
			throw e;
		} finally {
			this.releaseSession(ses);
		}
		return pos;
	}

	/**
	 * 执行HQL语句
	 * @athor tangkf
	 * @date 2008-1-5
	 */
	public int executeHql(String hql) throws Exception {
		return executeHql(hql, new Object[] {});
	}

	/**
	 * 执行一批HQL语句，执行完成后立即 flush
	 * @author 汤垲峰 2009-12-27
	 * @param hql
	 * @return
	 */
	public int executeBatchHql(String[] hqls) throws Exception {
		int rtn = 0;
		Session ses = this.getSession();
		Transaction tx = null;
		try {
			tx = ses.beginTransaction();
			for (String hql : hqls) {
				Query q = ses.createQuery(hql);
				q.executeUpdate();
			}
			tx.commit();
		} catch (RuntimeException e) {
			tx.rollback();
			rtn = 1;
			throw e;
		} finally {
			this.releaseSession(ses);
		}
		return rtn;
	}

	/**
	 * 执行HQL语句，并传入参数表
	 * @author tangkf ---2012-1-13
	 * @param hql
	 * @param params
	 * @return
	 */
	public int executeHql(String hql, Object[] params) throws Exception {
		int r = 0;
		Session ses = this.getSession();
		Transaction tx = null;
		try {
			tx = ses.beginTransaction();
			Query q = ses.createQuery(hql);
			if (params != null && params.length != 0) {
				for (int i = 0; i < params.length; i++) {
					q.setParameter(i, params[i]);
				}
			}
			q.executeUpdate();
			tx.commit();
		} catch (RuntimeException e) {
			r = 1;
			if (null != tx) {
				tx.rollback();
			}
			throw e;
		} finally {
			ses.close();
		}
		return r;
	}

	/**
	 * 执行HQL语句，并传入参数表
	 * @author tangkf ---2012-1-13
	 * @param hql
	 * @param params
	 * @return
	 */
	public int executeHql(String hql, Map<String, Object> params) throws Exception {
		int r = 0;
		Session ses = this.getSession();
		Transaction tx = null;
		try {
			tx = ses.beginTransaction();
			Query query = ses.createQuery(hql);
			if (params != null) {
				Map<String, Object> mparam = (Map<String, Object>) params;
				Iterator<Entry<String, Object>> it = mparam.entrySet().iterator();
				while (it.hasNext()) {
					Entry<String, Object> e = it.next();
					String key = e.getKey();
					Object value = mparam.get(key);
					if (value instanceof Collection<?>) {
						query.setParameterList(key, (Collection<?>) value);
					} else if (value instanceof Object[]) {
						query.setParameterList(key, (Object[]) value);
					} else {
						query.setParameter(key, value);
					}
				}
			}
			query.executeUpdate();
			tx.commit();
		} catch (RuntimeException e) {
			r = 1;
			if (null != tx) {
				tx.rollback();
			}
			throw e;
		} finally {
			ses.close();
		}
		return r;
	}

	/**
	 * <P>
	 * 将一个 count 查询的hql语句的值直接返回
	 * </P>
	 * @author 汤垲峰 2009-4-27
	 * @param hql
	 * @return
	 */
	public long countHql(String hql) throws Exception {
		long rtn = 0;
		List<?> rlst = queryAll(hql);
		if (CommonUtils.notEmpty(rlst)) {
			Object obs = rlst.get(0);
			if (obs != null) {
				rtn = Long.parseLong("" + obs);
			}
		}
		return rtn;
	}

	/**
	 * @see com.xteam.db.IBasePageDao#query(java.lang.Class, java.lang.String, int, int)
	 */
	public <T> PageObjectSet<T> query(Class<T> clazz, String countHql, int firstResult, int maxResult) throws Exception {
		if (CommonUtils.isEmpty(countHql)) {
			countHql = IBaseDao.SQL_SELECT + " count(*) " + IBaseDao.SQL_FROM + clazz.getName();
		}
		return this.queryPage(IBaseDao.SQL_FROM + clazz.getName(), countHql, null, firstResult, maxResult, false);
	}

	/**
	 * 分页参数
	 */
	public <T> PageObjectSet<T> query(String hql, String countHql, Map<String, Object> paramMap, int firstResult, int maxResult) throws Exception {
		return this.queryPage(hql, countHql, null, firstResult, maxResult, false);
	}

	/**
	 * 分页参数
	 * @throws Exception
	 */
	public <T> PageObjectSet<T> query(String hql, String countHql, int index, int size) throws Exception {
		return this.queryPage(hql, countHql, null, index, size, false);
	}

	/**
	 * 分页查询记录
	 * @throws Exception
	 */
	public <T> PageObjectSet<T> query(Class<T> clazz, Page page) throws Exception {
		String countHql = page.getRowCountHQL();
		if (CommonUtils.isEmpty(countHql)) {
			countHql = IBaseDao.SQL_SELECT + " count(*) " + IBaseDao.SQL_FROM + clazz.getName();
		}
		return this.queryPage(IBaseDao.SQL_FROM + clazz.getName(), countHql, null, page.getIndex(), page.getSize(), false);
	}

	/**
	 * 返回分页记录
	 * @see com.IBasePageDao.commons.dbserver.IDBUPage#query(java.lang.String, String, String, java.util.Map, com.xteam.db.page.toms.commons.dbserver.page.Page)
	 */
	public <T> PageObjectSet<T> query(String hql, Map<String, Object> parametersMap, Page page) throws Exception {
		return this.queryPage(hql, page.getRowCountHQL(), parametersMap, page.getIndex(), page.getSize(), false);
	}

	/**
	 * @see com.xteam.db.IBasePageDao#query(java.lang.String, java.lang.Object[], com.xteam.db.page.Page)
	 */
	public <T> PageObjectSet<T> query(String hql, Object[] params, Page page) throws Exception {
		return this.queryPage(hql, page.getRowCountSQL(), params, page.getIndex(), page.getSize(), false);
	}

	/**
	 * 返回分页记录
	 * @see com.xteam.db.IBasePageDao#query(java.lang.String, String, java.lang.Object[], int, int)
	 */
	public <T> PageObjectSet<T> query(String hql, String countHql, Object[] params, int firstResult, int maxResult) throws Exception {
		return this.queryPage(hql, countHql, params, firstResult, maxResult, false);
	}

	/**
	 * 返回分页记录
	 * @throws Exception
	 */
	public <T> PageObjectSet<T> query(String hql, Page page) throws Exception {
		return this.queryPage(hql, page.getRowCountHQL(), null, page.getIndex(), page.getSize(), false);
	}

	/**
	 * @athor tangkf
	 * @date 2008-1-4
	 */
	public <T> List<T> queryAll(Class<T> clazz) throws Exception {
		List<T> lst = null;
		lst = this.queryAll(SQL_FROM + clazz.getName());
		return lst;
	}

	/**
	 * 由条件参数HQL语句查询结果
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> queryAll(String hql, Map<String, Object> paramMap) throws Exception {
		Session ses = this.getSession();
		List<T> list = null;
		try {
			Query query = ses.createQuery(hql);
			if (paramMap != null) {
				Iterator<Entry<String, Object>> it = paramMap.entrySet().iterator();
				while (it.hasNext()) {
					Entry<String, Object> e = it.next();
					String key = e.getKey();
					Object value = paramMap.get(key);
					if (value instanceof Collection<?>) {
						query.setParameterList(key, (Collection<?>) value);
					} else if (value instanceof Object[]) {
						query.setParameterList(key, (Object[]) value);
					} else {
						query.setParameter(key, value);
					}
				}
			}
			list = query.list();
		} catch (RuntimeException e) {
			throw e;
		} finally {
			this.releaseSession(ses);
		}
		return list;
	}

	/**
	 * 由条件参数HQL语句查询结果
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> queryAll(String hql, Object[] params) throws Exception {
		Session ses = this.getSession();
		List<T> list = null;
		try {
			Query query = ses.createQuery(hql);
			if (params != null) {
				Object[] oparam = (Object[]) params;
				for (int i = 0; i < oparam.length; i++) {
					query.setParameter(i, oparam[i]);
				}
			}
			list = query.list();
		} catch (RuntimeException e) {
			throw e;
		} finally {
			this.releaseSession(ses);
		}
		return list;
	}

	/**
	 * 执行一个HQL语句并返回结果
	 * @see com.xteam.db.IBaseDao#queryAll(java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> queryAll(String hql) throws Exception {
		Session ses = this.getSession();
		List<T> list = null;
		try {
			Query query = ses.createQuery(hql);
			list = query.list();
		} catch (RuntimeException e) {
			throw e;
		} finally {
			this.releaseSession(ses);
		}
		return list;
	}

	/**
	 * 分页查询，显示的要求传递 记录总数统计SQL语句
	 * @author tangkf ---2012-1-13
	 * @param <T>
	 * @param sql 查询数据的SQL语句
	 * @param countSql 查询记录数的SQL语句
	 * @param params
	 * @param index
	 * @param size
	 * @param queryRowCount 是否强制执行总记录数查询
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private <T> PageObjectSet<T> queryPageSql(String sql, String countSql, Object params, int index, int size, boolean queryRowCount) throws Exception {
		Session ses = this.getSession();
		PageObjectSet<T> pos = null;
		try {
			List<T> list = null;
			long rowCount = 0;
			SQLQuery query = ses.createSQLQuery(sql);

			// 参数设置
			if (params != null) {
				if (params instanceof Map) {
					Map<String, Object> mparam = (Map<String, Object>) params;
					Iterator<Entry<String, Object>> it = mparam.entrySet().iterator();
					while (it.hasNext()) {
						Entry<String, Object> e = it.next();
						String key = e.getKey();
						Object value = mparam.get(key);
						query.setParameter(key, value);
					}
				} else if (params instanceof Object[]) {
					Object[] oparam = (Object[]) params;
					for (int i = 0; i < oparam.length; i++) {
						query.setParameter(i, oparam[i]);
					}
				}
			}

			// 设置分页信息
			query.setFirstResult(index);
			query.setMaxResults(size);

			// 执行查询
			list = query.list();

			// 计算总记录数
			if (!CommonUtils.isEmpty(countSql)) {
				rowCount = this.countHql(countSql);
			} else if (queryRowCount) {
				// 如果没有总记录数语句，且强制执行了总记录数查询
				// TODO 强制查询总记录数
			}

			// 组装分页对象
			pos = new PageObjectSet<T>(rowCount, list);
			pos.setResultList(list);
			pos.setIndex(index);
			pos.setSize(size); // 设置后可自动计算页数

		} catch (RuntimeException e) {
			throw e;
		} finally {
			this.releaseSession(ses);
		}
		return pos;
	}

	/**
	 * <P>
	 * 将一个 count 查询的sql语句的值直接返回
	 * </P>
	 * @author 汤垲峰 2009-4-27
	 * @param sql
	 * @return
	 */
	public long countSql(String sql) throws Exception {
		long rtn = 0;
		List<?> rlst = queryAllSql(sql);
		if (CommonUtils.notEmpty(rlst)) {
			Object obs = rlst.get(0);
			if (obs != null) {
				rtn = Long.parseLong("" + obs);
			}
		}
		return rtn;
	}

	/**
	 * 执行SQL并返回结果
	 */
	public <T> PageObjectSet<T> querySql(String sql, String countSql, int firstResult, int maxResult) throws Exception {
		return this.queryPageSql(sql, countSql, null, firstResult, maxResult, false);
	}

	/**
	 * 执行SQL并返回结果
	 */
	public <T> PageObjectSet<T> querySql(String sql, String countSql, Object[] parms, int firstResult, int maxResult) throws Exception {
		return this.queryPageSql(sql, countSql, parms, firstResult, maxResult, false);
	}

	/**
	 * 执行一个带条件和分页的SQL语句
	 * @author 汤垲峰 2009-12-27
	 * @param sql
	 * @param parms
	 * @param firstResult
	 * @param maxResult
	 * @return
	 */
	public <T> PageObjectSet<T> querySql(String sql, String countSql, Map<String, Object> parms, int firstResult, int maxResult) throws Exception {
		return this.queryPageSql(sql, countSql, parms, firstResult, maxResult, false);
	}

	/**
	 * 执行分页SQL查询
	 */
	public <T> PageObjectSet<T> querySql(String sql, String countSql, Object[] parms, Page page) throws Exception {
		return this.queryPageSql(sql, countSql, parms, page.getIndex(), page.getSize(), false);
	}

	/**
	 * 执行一个带条件MAP的SQL语句
	 * @author 汤垲峰 2009-12-27
	 * @param sql
	 * @param parms
	 * @param page
	 * @return
	 */
	public <T> PageObjectSet<T> querySql(String sql, String countSql, Map<String, Object> parms, Page page) throws Exception {
		return this.queryPageSql(sql, countSql, parms, page.getIndex(), page.getSize(), false);
	}

	/**
	 * 分页查询SQL
	 */
	public <T> PageObjectSet<T> querySql(String sql, String countSql, Page page) throws Exception {
		return this.queryPageSql(sql, countSql, null, page.getIndex(), page.getSize(), false);
	}

	/**
	 * 按SQL语句查询
	 * @athor tangkf
	 * @date 2008-1-4
	 */
	public <T> List<T> queryAllSql(String sql) throws Exception {
		return queryAllSql(sql, new Object[] {});
	}

	/**
	 * 执行指定了条件MAP的SQL语句，参数采用 字符串作为 占位符
	 * @author 汤垲峰 2009-12-27
	 * @param sql
	 * @param parms
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> queryAllSql(String sql, Map<String, Object> parms) throws Exception {
		Session ses = this.getSession();
		List<?> list = null;
		try {
			SQLQuery query = ses.createSQLQuery(sql);
			if (parms != null) {
				Iterator<Entry<String, Object>> it = parms.entrySet().iterator();
				while (it.hasNext()) {
					Entry<String, Object> e = it.next();
					String key = e.getKey();
					Object value = parms.get(key);
					if (value instanceof Collection<?>) {
						query.setParameterList(key, (Collection<?>) value);
					} else if (value instanceof Object[]) {
						query.setParameterList(key, (Object[]) value);
					} else {
						query.setParameter(key, value);
					}
				}
			}
			list = query.list();
			ses.clear();
		} catch (RuntimeException e) {
			throw e;
		} finally {
			this.releaseSession(ses);
		}
		return (List<T>) list;
	}

	/**
	 * 执行指定了条件参数的SQL语句，参数采用 字符串作为 占位符
	 * @author 汤垲峰 2009-12-27
	 * @param sql
	 * @param parms
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> queryAllSql(String sql, Object[] params) throws Exception {
		Session ses = this.getSession();
		List<?> list = null;
		try {
			SQLQuery query = ses.createSQLQuery(sql);
			if (params != null) {
				Object[] oparam = (Object[]) params;
				for (int i = 0; i < oparam.length; i++) {
					query.setParameter(i, oparam[i]);
				}
			}
			list = query.list();
			ses.clear();
		} catch (RuntimeException e) {
			throw e;
		} finally {
			this.releaseSession(ses);
		}
		return (List<T>) list;
	}

	/**
	 * 执行SQL语句
	 * @athor tangkf
	 * @date 2008-1-4
	 */
	public int executeSql(String sql) throws Exception {
		return executeSql(sql, null);
	}

	/**
	 * 执行SQL语句，并传入参数表
	 * @author tangkf ---2012-1-13
	 * @param sql
	 * @param params
	 * @return
	 */
	public int executeSql(String sql, Object[] params) throws Exception {
		int r = 0;
		Session ses = this.getSession();
		Transaction tx = null;
		try {
			tx = ses.beginTransaction();
			Query q = ses.createSQLQuery(sql);
			if (params != null && params.length != 0) {
				for (int i = 0; i < params.length; i++) {
					q.setParameter(i, params[i]);
				}
			}
			q.executeUpdate();
			tx.commit();
		} catch (RuntimeException e) {
			r = 1;
			if (null != tx) {
				tx.rollback();
			}
			throw e;
		} finally {
			ses.close();
		}
		return r;
	}

	/**
	 * 批量执行SQL语句，批量执行完成后立即 flush
	 * @author 汤垲峰 2009-12-27
	 * @param sqls
	 * @return
	 */
	public int executeBatchSql(String[] sqls) throws Exception {
		int r = 0;
		Session ses = this.getSession();
		Transaction tx = ses.beginTransaction();
		try {
			for (String sql : sqls) {
				SQLQuery query = ses.createSQLQuery(sql);
				query.executeUpdate();
			}
			tx.commit();
		} catch (RuntimeException e) {
			r = 1;
			tx.rollback();
			throw e;
		} finally {
			this.releaseSession(ses);
		}
		return r;
	}

}